# -*- coding: utf-8 -*-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：get_stock_gap.py
#日期：2020-10-21
#备注：跳空高开7天不回补股票
CREATE TABLE `stock_gap` (
  `gap_date` varchar(10) NOT NULL COMMENT '跳空开始日',
  `stock_no` varchar(10) NOT NULL COMMENT '股票代码',
  `stock_name` varchar(10) DEFAULT NULL COMMENT '股票简称',
  `gap_price` float DEFAULT '0' COMMENT '跳空开始日最高价',
  `gap_is` varchar(2) DEFAULT '否' COMMENT '跳空缺口是否回补',
  `gap_is_price` float DEFAULT '0' COMMENT '回补缺口最低价',
  `gap_is_date` varchar(10) DEFAULT NULL COMMENT '回补缺口日期',
  `target_date` varchar(32) NOT NULL COMMENT '第7预计交易日期',
  `create_time` datetime DEFAULT NULL,
  PRIMARY KEY (`stock_no`,`gap_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='7天内跳空不补缺股票统计表';

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
from pycacho.cachobase.chinese_workday import get_trade_day
from pacong.stock.stock_config import db,cursor

# T日统计今天跳空高开股票：截止到统计日7日内有涨幅大于8的情况
def gap_stock(start_date,target_date,yet_table,tdy_table):
    sql1 = 'delete from stock_gap where gap_date=%s;' % (start_date)
    cursor.execute(sql1)
    db.commit()
    sql = 'INSERT INTO stock_gap select a.trade_date,a.stock_no,a.stock_name,a.price_max,"否",0.00,NULL,"%s",NOW() from %s a,%s b where a.stock_no=b.stock_no and a.price_chg>0 and a.price_new>=a.price_today and b.price_chg>0 and b.price_new>=b.price_today and a.price_max<b.price_min and b.turnover>a.turnover*1.8 ' \
          'and a.stock_no in (select stock_no from stock_tradeinfo_total where price_chg>8 and trade_date>"%s");' %(target_date,yet_table,tdy_table,get_trade_day(7,'s'))
    cursor.execute(sql)
    print('符合要求股票的日期：',yet_table)
    db.commit()


def get_gap_stock():
    for n in range(0,1):  # N+2天开始
        yet_table = 'stock_tradeinfo_' + get_trade_day(n+1,'s')
        tdy_table = 'stock_tradeinfo_' + get_trade_day(n,'s')
        gap_stock(get_trade_day(n+1,'s'),get_trade_day(n-5,'s'),yet_table,tdy_table)

# 未补缺口的股票
def gap_is(gap_date):
    sql = 'select stock_no as 股票代码,stock_name as 股票名称,gap_price as 跳空开始日最高价 from stock_gap where gap_date="%s" and gap_is="否" order by stock_no ;' %(gap_date)
    cursor.execute(sql)
    results = cursor.fetchall()
    stocks = []
    for stock in results:
        row =[]
        for info in stock:
            row.append(info)
        stocks.append(row)
    #print(gap_date,stocks)
    return stocks

# 更新7日目标日期最低价target_price、并且判断是否符合要求：最低价不破跳空缺口
def gap_mysql(trade_date,stock_no,trade_date1,gap_date,stock_no1):
    sql1= "update stock_gap set gap_is_price=(select price_min from stock_tradeinfo_total where trade_date='%s' and stock_no='%s'),gap_is_date='%s'" \
          "where gap_is='否' and gap_date='%s' and stock_no='%s';"%(trade_date,stock_no,trade_date1,gap_date,stock_no1)
    cursor.execute(sql1)
    db.commit()
    sql2= "update stock_gap set gap_is='是' where (gap_is_price<=gap_price or gap_is_price>gap_price*1.3) and gap_is='否' and gap_date='%s' and stock_no='%s';" %(gap_date,stock_no1)
    cursor.execute(sql2)
    db.commit()


def update_exist_gap(n):
    gap_date = get_trade_day(n,'s')     # 开始日
    stocks = gap_is(gap_date)
    for stock in stocks:
        stock_no = stock[0]
        for i in range(2,8):
            if n>i-1:
                trade_date = get_trade_day(n-i,'s')  # 后续6天表现
                print('启动日期：计划买入日期',gap_date,stock_no,trade_date)
                gap_mysql(trade_date,stock_no,trade_date,gap_date,stock_no)

def update_gap():
    pass


if __name__ == '__main__':
    get_gap_stock()
    for i in range(0,7):           # 0,7
        update_exist_gap(i)
